package com.bright.ghj.overt.service.impl;

import com.bright.ghj.common.pojo.dto.PageDTO;
import com.bright.ghj.common.pojo.vo.PageVO;
import com.bright.ghj.common.util.DateUtil;
import com.bright.ghj.common.util.PageUtil;
import com.bright.ghj.common.util.StringUtil;
import com.bright.ghj.overt.constant.DataConstant;
import com.bright.ghj.overt.constant.PublicConstant;
import com.bright.ghj.overt.converter.AttachedConverter;
import com.bright.ghj.overt.converter.FileItemConverter;
import com.bright.ghj.overt.converter.OpenReportRecordConverter;
import com.bright.ghj.overt.enums.PublicType;
import com.bright.ghj.overt.manager.BaseDataManager;
import com.bright.ghj.overt.manager.DistZtManager;
import com.bright.ghj.overt.manager.FileListManager;
import com.bright.ghj.overt.manager.OpenReportRecordManager;
import com.bright.ghj.common.pojo.bo.ExcelReadFileItem;
import com.bright.ghj.overt.pojo.command.ImportAccessCommand;
import com.bright.ghj.overt.pojo.dto.*;
import com.bright.ghj.overt.pojo.po.primary.Attached;
import com.bright.ghj.overt.pojo.po.primary.FlowLog;
import com.bright.ghj.overt.pojo.po.primary.NavigateButtonCwgk;
import com.bright.ghj.overt.pojo.query.OpenReportRecordQuery;
import com.bright.ghj.overt.pojo.query.TableDataIdQuery;
import com.bright.ghj.overt.pojo.query.TableDataQuery;
import com.bright.ghj.overt.pojo.vo.*;
import com.bright.ghj.overt.repository.primary.AttachedRepository;
import com.bright.ghj.overt.repository.primary.FlowLogRepository;
import com.bright.ghj.overt.service.BaseDataService;
import com.bright.ghj.overt.service.listener.OvertExcelReadListener;
import com.bright.ghj.overt.util.AccessDBUtil;
import com.bright.ghj.overt.util.EntityManagerUtil;
import com.bright.ghj.common.util.excel.ExcelUtil;
import com.bright.ghj.common.util.ListUtil;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ClassUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.Statement;
import java.util.*;
import java.util.function.Consumer;

/**
 * @Author hxj
 * @Date 2023/12/20 17:13
 * @Description
 */

@Component
@RequiredArgsConstructor
@Slf4j
public class BaseDataServiceImpl implements BaseDataService {

    private final FileListManager fileListManager;
    private final BaseDataManager baseDataManager;
    private final OpenReportRecordManager openReportRecordManager;

    private final DistZtManager distZtManager;
//    private final AsyncMessageService asyncMessageService;
    private final AttachedRepository attachedRepository;
    private final FileItemConverter fileItemConverter;
    private final OpenReportRecordConverter openReportRecordConverter;
    private final AttachedConverter attachedConverter;
    private final ObjectMapper objectMapper;

//    private final JdbcTemplate jdbcTemplatePrimary;
//    private final PlatformTransactionManager transactionManagerPrimary;

    private final FlowLogRepository flowLogRepository;

    @PersistenceContext
    private final EntityManager entityManager;

    private final DataConstant dataConstant;

    @Override
    public List<TableHeaderVO> listTableHeaders(String tableName) {
        List<FileItemDTO> fileItemDTOS = fileListManager.listFileItemsByCache(tableName);
        List<TableHeaderVO> tableHeaderVOS = fileItemConverter.dtoConvertVO(fileItemDTOS);
        // 计算fieldLen总宽度值
        int totalFieldLen = tableHeaderVOS.stream().mapToInt(TableHeaderVO::getFieldLen).sum();

        for (int i = 0; i < fileItemDTOS.size(); i++) {
            TableHeaderVO tableHeaderVO = tableHeaderVOS.get(i);
            FileItemDTO fileItemDTO = fileItemDTOS.get(i);
            // 设值对齐方式及字段类型
            tableHeaderVO.setType(fileItemDTO.getFieldType().getValue());
            tableHeaderVO.setAlign(fileItemDTO.getFieldAlign().getValue());

            // 重新计算表头宽度 按比例分配
            if (totalFieldLen == 0) {
                tableHeaderVO.setFieldLen(100);
            } else {
                int fieldLen = (int) (((double) tableHeaderVO.getFieldLen()) / totalFieldLen * dataConstant.FILEITEM_TOTAL_WIDTH);
                // 防止字段太多 分下来每个字段太窄 判断如果调整后的fieldLen比原先要小 则不调整
                if (fieldLen > tableHeaderVO.getFieldLen()) {
                    tableHeaderVO.setFieldLen(fieldLen);
                }
            }

        }

        return tableHeaderVOS;
    }

    @Override
    public TablePageVO<Map<String, Object>> pageTableData(TableDataQuery tableDataQuery) {
        return getTableData(tableDataQuery, true);
    }

    /*
        isPage 是否分页 默认为true
     */
    public TablePageVO<Map<String, Object>> getTableData(TableDataQuery tableDataQuery, boolean isPage) {

        FileListDTO fileList = fileListManager.getFileListByCache(tableDataQuery.getTableName());
        List<FileItemDTO> fileItems = fileListManager.listFileItemsByCache(tableDataQuery.getTableName());
        MainModalDTO mainModal = getMainModal(tableDataQuery);

        if (fileList == null || fileItems.size() == 0) {
            throw new RuntimeException(tableDataQuery.getTableName() + " 取不到对应FileList或表头，请检查！");
        }

        TablePageVO<Map<String, Object>> result = new TablePageVO<>();
        if (isPage) {
            PageDTO<Map<String, Object>> mapPageDTO = baseDataManager.pageTableData(tableDataQuery, fileList, fileItems);
            result.setTotal(mapPageDTO.getTotal());
            result.setPageCount(mapPageDTO.getPageCount());
            result.setData(mapPageDTO.getData());
        } else {
            // 不分页的情况 一次过返回全部数据 但为了要返回子表 也使用TablePageVO来返回 只是只有1页
            List<Map<String, Object>> mapList = baseDataManager.listTableData(tableDataQuery, fileList, fileItems);
            result.setTotal((long) mapList.size());
            result.setPageCount(1);
            result.setData(mapList);
        }

        // 有子表的情况
        if (mainModal.getSubMainModalList() != null) {
            result.setSubData(getSubTableData(tableDataQuery, fileList, mainModal));
        }

        return result;
    }

    private MainModalDTO getMainModal(TableDataQuery tableDataQuery) {
//        return fileListManager.getMainModalById(tableDataQuery.getMainModalId());
        return fileListManager.getMainModalByTableName(tableDataQuery.getTableName());
    }

    private MainModalDTO getMainModal(TableDataDTO tableDataDTO) {
        TableDataQuery tableDataQuery = new TableDataQuery();
//        tableDataQuery.setMainModalId(tableDataDTO.getMainModalId());
        tableDataQuery.setTableName(tableDataDTO.getTableName());
        return getMainModal(tableDataQuery);
    }

    @Override
    public TablePageVO<Map<String, Object>> pageTableData(TableDataIdQuery tableDataIdQuery) {
        // 这里补充tableDataQuery缺少的参数 给通用方法查询
        TableDataQuery tableDataQuery = getTableDataQuery(tableDataIdQuery);
        return pageTableData(tableDataQuery);
    }

    @Override
    public TablePageVO<Map<String, Object>> listTableData(TableDataQuery tableDataQuery) {
        return getTableData(tableDataQuery, false);
    }

    @Override
    public TablePageVO<Map<String, Object>> listTableData(TableDataIdQuery tableDataIdQuery) {
        // 这里补充tableDataQuery缺少的参数
        TableDataQuery tableDataQuery = getTableDataQuery(tableDataIdQuery);
        return getTableData(tableDataQuery, false);
    }

    private TableDataQuery getTableDataQuery(TableDataIdQuery tableDataIdQuery) {
        TableDataQuery tableDataQuery = new TableDataQuery();
        OpenReportRecordDTO openReportRecord = openReportRecordManager.getById(tableDataIdQuery.getId());
        tableDataQuery.setYears(openReportRecord.getYears());
        tableDataQuery.setMonths(openReportRecord.getMonths());
        tableDataQuery.setDays(openReportRecord.getDays());
        tableDataQuery.setSno(openReportRecord.getSno());
        tableDataQuery.setZtId(openReportRecord.getZtId());
        tableDataQuery.setTableName(openReportRecord.getTableName());
        tableDataQuery.setDistNo(openReportRecord.getDistNo());
        tableDataQuery.setUserDistNo(tableDataIdQuery.getUserDistNo());
//        tableDataQuery.setMainModalId(tableDataIdQuery.getMainModalId());
        tableDataQuery.setPageNumber(tableDataIdQuery.getPageNumber());
        tableDataQuery.setPageSize(tableDataIdQuery.getPageSize());
        tableDataQuery.setSorts(tableDataIdQuery.getSorts());
        return tableDataQuery;
    }

    private FileListDTO getSubFileList(String tableName, FileListDTO parentFileList) {
        FileListDTO subFileList = fileListManager.getFileListByCache(tableName);
        // 保证子表的公开类型与主表一致
        subFileList.setPublicType(parentFileList.getPublicType());
        return subFileList;
    }

    @Override
    @Transactional(rollbackFor = Throwable.class)
    public SaveResultVO saveTableData(TableDataDTO tableDataDTO) throws Exception {
        SaveResultVO saveResultVO = new SaveResultVO();
        // 兼容两种传值方式 可以只传openReportRecord的id 也可直接传年月账套号表名
        if (tableDataDTO.getId() != null && tableDataDTO.getId() != 0) {
            // 只传open表id的情况 从数据库获取年月账套号表名封装到tableDataDTO
            OpenReportRecordDTO openReportRecord = openReportRecordManager.getById(tableDataDTO.getId());

            // 校验该数据是否不允许编辑
            if (!openReportRecordManager.isEditEnable(openReportRecord)) {
                getCanNotEditMessage(openReportRecord, saveResultVO);
                // v1.0.7 子表不允许修改 也要保存脚注
                saveTableFooter(tableDataDTO, saveResultVO);
                return saveResultVO;
            }

            setTableDataDTOByOpenReportRecord(tableDataDTO, openReportRecord);
        } else {
            // 不传id的情况 根据年月账套号判断是否已审核
            OpenReportRecordQuery query = new OpenReportRecordQuery();
            query.setYears(tableDataDTO.getYears());
            query.setMonths(tableDataDTO.getMonths());
            query.setZtId(tableDataDTO.getZtId());
            query.setTableName(tableDataDTO.getTableName());
            query.setSno(tableDataDTO.getSno());
            List<OpenReportRecordDTO> openList = openReportRecordManager.listByCondition(query, PublicConstant.LIST_TYPE_EDIT);
            for (OpenReportRecordDTO openDTO : openList) {
                if (!openReportRecordManager.isEditEnable(openDTO)) {
                    getCanNotEditMessage(openDTO, saveResultVO);
                    // v1.0.7 子表不允许修改 也要保存脚注
                    saveTableFooter(tableDataDTO, saveResultVO);
                    return saveResultVO;
                }
            }
        }

        FileListDTO fileList = fileListManager.getFileListByCache(tableDataDTO.getTableName());
        List<FileItemDTO> fileItems = fileListManager.listFileItemsByCache(tableDataDTO.getTableName());
        MainModalDTO mainModal = getMainModal(tableDataDTO);

        if (fileList == null || fileItems.size() == 0) {
            saveResultVO.setSuccess(false);
            saveResultVO.getInfoList().add(tableDataDTO.getTableName() + " 取不到对应FileList或表头，请检查！");
        }

        if (!saveResultVO.isSuccess()) {
            return saveResultVO;
        }

        saveResultVO = baseDataManager.saveTableData(tableDataDTO, fileList, fileItems);


        // 保存子表数据
        if (mainModal.getSubMainModalList() != null) {
            saveSubTableData(tableDataDTO, mainModal, saveResultVO);
        }

        // 是否报审
//        boolean isReadyAudit = tableDataDTO.isReadyAudit();
//        if (isReadyAudit) {
//            openReportRecordManager.setReadyAuditStatus(openReportRecord);
//        }


        // 保存脚注
        saveTableFooter(tableDataDTO, saveResultVO);

        return saveResultVO;
    }

    // 保存脚注
    private void saveTableFooter(TableDataDTO tableDataDTO, SaveResultVO saveResultVO) {
        // 要么全部zt表的 要么全部open表的？

        Map<String, Object> footerMap = tableDataDTO.getFooterMap(); // key=footerKey value=脚注值
        if (footerMap == null || footerMap.size() == 0) return;

        List<FilelistCwgkFooterDto> footerConfigs = fileListManager.getFooterByFileName(tableDataDTO.getTableName());
        // 收集所有要更新的字段
        Map<String, Object> ztNeedUpdateData = new HashMap<>(), openReportRecordNeedUpdateData = new HashMap<>();
        for (FilelistCwgkFooterDto footerConfig : footerConfigs) {
            String footerValueNeedUpdate = (String) footerMap.get(footerConfig.getFooterKey());

            if (footerValueNeedUpdate == null) continue;
            if (StringUtil.isNotEmpty(footerValueNeedUpdate)) {
                if (footerConfig.getIsFromZt()) {
                    ztNeedUpdateData.put(footerConfig.getZtFieldName(), footerValueNeedUpdate);
                } else {
                    openReportRecordNeedUpdateData.put(footerConfig.getOpenReportRecordFieldName(), footerValueNeedUpdate);
                    if (StringUtil.isNotEmpty(footerConfig.getZtFieldName())) {
                        // 取open表的脚注配置ztFieldName 则保存脚注的时候也会同步修改zt表的相关字段
                        ztNeedUpdateData.put(footerConfig.getZtFieldName(), footerValueNeedUpdate);
                    }
                }
            }
        }

        // 更新zt表和open表相关字段
        distZtManager.updateZtParamsByZtId(tableDataDTO.getZtId(), ztNeedUpdateData);
        if (tableDataDTO.getId() != null) {
            openReportRecordManager.updateParamsById(tableDataDTO.getId(), openReportRecordNeedUpdateData);
        }


    }

    // 获取不可编辑的错误提示语
    private void getCanNotEditMessage(OpenReportRecordDTO openReportRecordDTO, SaveResultVO saveResultVO) {
//        saveResultVO.setSuccess(false);
//        saveResultVO.getInfoList().add(String.format("%s %s %s",
//                openReportRecordDTO.getId(), openReportRecordDTO.getTitle(), PublicConstant.NOT_EDIT_ENABLE_MESSAGE));
        // v1.0.7 为了兼容脚注保存 这里先改为返回成功 但返回信息声明只保存脚注
        saveResultVO.setSuccess(true);
        saveResultVO.setMessage("保存成功（只保存脚注签名，数据已完成审核或只能由财务转入，不允许修改）");
    }

    private void setTableDataDTOByOpenReportRecord(TableDataDTO tableDataDTO, OpenReportRecordDTO openReportRecord) {
        tableDataDTO.setYears(openReportRecord.getYears());
        tableDataDTO.setMonths(openReportRecord.getMonths());
        tableDataDTO.setDays(openReportRecord.getDays());
        tableDataDTO.setSno(openReportRecord.getSno());
        tableDataDTO.setZtId(openReportRecord.getZtId());
        tableDataDTO.setTableName(openReportRecord.getTableName());
        tableDataDTO.setDistNo(openReportRecord.getDistNo());
    }

    @Override
    public PageVO<OpenReportRecordVO> listOpenReportRecordByPage(OpenReportRecordQuery query, int listType) {
        // 转换数据状态 前台传值跟后台status值并不一一对应 转换成实际的query查询条件
        if (query.getStatus() != null) {
//            List<Integer> statusList = ListUtil.toIntegerListByArray(query.getStatus());
//            if (statusList.size() > 0) {
            // 只取第一个值
            int status = query.getStatus();

            switch (status) {
                case 2:
                    // 这里筛选未审核+已审核未通过
                    query.setIsAuditOK(false);
                    query.setIsAudit(null);
                    query.setIsPublish(null);
                    break;
                case 4:
                case 8:
                    // 已通过审核
                    query.setIsAudit(true);
                    query.setIsAuditOK(true);
                    query.setIsPublish(null);
                    break;
                case 6:
                    // 未审核
                    query.setIsAudit(false);
                    query.setIsAuditOK(null);
                    query.setIsPublish(null);
                    break;
                case 10:
                    // 不通过审核
                    query.setIsAudit(true);
                    query.setIsAuditOK(false);
                    query.setIsPublish(null);
                    break;
                case 12:
                    // 未发布
                    query.setIsAudit(null);
                    query.setIsAuditOK(null);
                    query.setIsPublish(false);
                    break;
                case 14:
                    // 已发布
                    query.setIsAudit(null);
                    query.setIsAuditOK(null);
                    query.setIsPublish(true);
                    break;
                default:
                    break;
            }
//            }
            query.setStatus(null);
        }

        PageDTO<OpenReportRecordDTO> pageDTO = openReportRecordManager.pageByCondition(query, listType, PageUtil.toPageable(query));
        PageVO<OpenReportRecordVO> pageVO = new PageVO<>();
        pageVO.setData(openReportRecordConverter.dtoConvertVO(pageDTO.getData()));
        pageVO.setTotal(pageDTO.getTotal());
        pageVO.setPageCount(pageDTO.getPageCount());
        return pageVO;
    }

    @Override
    public TableInfoVO getTableInfo(TableDataIdQuery tableDataIdQuery) {
        TableInfoVO tableInfoVO = new TableInfoVO();
        OpenReportRecordDTO dto = openReportRecordManager.getById(tableDataIdQuery.getId());
        tableInfoVO.setYears(dto.getYears());
        tableInfoVO.setMonths(dto.getMonths());
        tableInfoVO.setDateStr(dto.getDateStr());
        tableInfoVO.setZtId(dto.getZtId());
        tableInfoVO.setZtName(dto.getZtName());
        tableInfoVO.setDescription(dto.getLongTitle());
        tableInfoVO.setTableName(dto.getTableName());
        tableInfoVO.setStatusFlag(dto.getStatusFlag());
        tableInfoVO.setId(dto.getId());
        tableInfoVO.setUploadDate(dto.getUploadDate());
        tableInfoVO.setUploadUser(dto.getUploadUser());
        FileListDTO fileList = fileListManager.getFileListByCache(dto.getTableName());
        if (fileList == null) {
            throw new RuntimeException("取不到FileList:" + dto.getTableName());
        }
        tableInfoVO.setPublicType(fileList.getPublicType().getValue());
        tableInfoVO.setEdit(openReportRecordManager.isEditEnable(dto));

        // 脚注赋值
        Map<String, Map<String, String>> footerMap = getFooterMap(dto, fileList);
        tableInfoVO.setFooterMap(footerMap);
        int i = 1;
        for (Map.Entry<String, Map<String, String>> entry : footerMap.entrySet()) {
            String value = entry.getValue().get("value");
            if (i == 1) tableInfoVO.setFooter1(value);
            if (i == 2) tableInfoVO.setFooter2(value);
            if (i == 3) tableInfoVO.setFooter3(value);
            if (i == 4) tableInfoVO.setFooter4(value);
            i++;
        }

        // 子表名称
        TableDataQuery tableDataQuery = new TableDataQuery();
        tableDataQuery.setTableName(dto.getTableName());
        MainModalDTO mainModal = getMainModal(tableDataQuery);
        if (mainModal.getSubMainModalList() == null) {
            tableInfoVO.setSubTableNames(new ArrayList<>());
            tableInfoVO.setSubTableDescriptions(new ArrayList<>());
        } else {
            tableInfoVO.setSubTableNames(ListUtil.toSingleList(mainModal.getSubMainModalList(), MainModalDTO::getTableName));
            tableInfoVO.setSubTableDescriptions(ListUtil.toSingleList(mainModal.getSubMainModalList(), MainModalDTO::getModalName));
        }

        // 返回审核标志、发布标志、审核是否成功标志
        tableInfoVO.setAudit(openReportRecordManager.isAudit(dto));
        tableInfoVO.setAuditOK(openReportRecordManager.isAuditOK(dto));
        tableInfoVO.setPublish(openReportRecordManager.isPublished(dto));

        return tableInfoVO;
    }

    private Map<String, Map<String, String>> getFooterMap(OpenReportRecordDTO dto, FileListDTO fileList) {
        ZtDTO zt = distZtManager.getZtByZtIdByCache(dto.getZtId());
        String tableName = dto.getTableName();

        LinkedHashMap<String, Map<String, String>> footerMap = new LinkedHashMap<>();
        List<FilelistCwgkFooterDto> footersConfig = fileListManager.getFooterByFileName(tableName);
        // 取脚注配置 如果没有配置 则使用默认脚注
//        if (footersConfig.size() > 0) {
            List<FilelistCwgkFooterDto> needFieldNameFooters = new ArrayList<>(16);
            for (FilelistCwgkFooterDto footer : footersConfig) {
                String footerValue;
                // 从zt表获取脚注字段
                if (footer.getIsFromZt()) {
                    String footerKey = footer.getFooterKey();
                    if (StringUtil.equalsIgnoreCase(footerKey, PublicConstant.DWFZR_KEY)) {
                        footerValue = zt.getDwfzr();
                    } else if (StringUtil.equalsIgnoreCase(footerKey, PublicConstant.CNXM_KEY)) {
                        footerValue = zt.getCnxm();
                    } else if (StringUtil.equalsIgnoreCase(footerKey, PublicConstant.KJXM_KEY)) {
                        footerValue = zt.getKjxm();
                    } else if (StringUtil.equalsIgnoreCase(footerKey, PublicConstant.CWFZR_KEY)) {
                        footerValue = zt.getCwfzr();
                    } else if (StringUtil.equalsIgnoreCase(footerKey, PublicConstant.JDXZ_KEY)) {
                        footerValue = zt.getJdxz();
                    } else {
                        footerValue = null;
                        needFieldNameFooters.add(footer);
                    }
                } else {
                    // 从open表获取脚注字段
                    String openReportRecordFieldName = footer.getOpenReportRecordFieldName();
                    if (StringUtil.equalsIgnoreCase(openReportRecordFieldName, "footer1")) {
                        footerValue = dto.getFooter1();
                    } else if (StringUtil.equalsIgnoreCase(openReportRecordFieldName, "footer2")) {
                        footerValue = dto.getFooter2();
                    } else if (StringUtil.equalsIgnoreCase(openReportRecordFieldName, "footer3")) {
                        footerValue = dto.getFooter3();
                    } else if (StringUtil.equalsIgnoreCase(openReportRecordFieldName, "footer4")) {
                        footerValue = dto.getFooter4();
                    } else {
                        footerValue = null;
                        needFieldNameFooters.add(footer);
                    }
                }
//                if (footerValue != null) {
                addFooterValueMap(footerMap, footer, footerValue);
//                }
            }
            // 从zt表或openReportRecord表获取额外的字段作为脚注
            if (needFieldNameFooters.size() > 0) {
                List<FilelistCwgkFooterDto> needFieldNamesFromZt = new ArrayList<>(16), needFieldNamesFromOpenReport = new ArrayList<>(16);
                for (FilelistCwgkFooterDto footer : needFieldNameFooters) {
                    if (footer.getIsFromZt()) {
                        needFieldNamesFromZt.add(footer);
                    } else {
                        needFieldNamesFromOpenReport.add(footer);
                    }
                }
                Map<String, Object> footerValues = new HashMap<>(16);
                if (needFieldNamesFromZt.size() > 0) {
                    footerValues.putAll(distZtManager.getZtFieldValueByFieldNames(zt.getZtId(),
                            ListUtil.toSingleList(needFieldNamesFromZt, FilelistCwgkFooterDto::getZtFieldName)));
                }
                if (needFieldNamesFromOpenReport.size() > 0) {
                    footerValues.putAll(openReportRecordManager.getFieldValueByFieldNames(dto.getId(),
                            ListUtil.toSingleList(needFieldNamesFromOpenReport, FilelistCwgkFooterDto::getOpenReportRecordFieldName)));
                }

                for (FilelistCwgkFooterDto footer : needFieldNameFooters) {
                    String footerFieldName = footer.getIsFromZt() ? footer.getZtFieldName() : footer.getOpenReportRecordFieldName();
                    String footerValue = (String) footerValues.get(footerFieldName);
                    addFooterValueMap(footerMap, footer, footerValue);
                }
            }
//        } else {
//            // 默认脚注
//            footerMap.put("单位负责人", zt.getDwfzr());
//            footerMap.put("会计", zt.getKjxm());
//            footerMap.put("出纳", zt.getCnxm());
//            footerMap.put("监督小组", zt.getJdxz());
//            footerMap.put("财务负责人", zt.getCwfzr());
//        }

        // v1.0.11 香洲特殊 加上“镇街审核” 记录审核这条数据的用户名
        if (dataConstant.ENABLE_FOOTER_AUDITOR_NAME) {
            // 判断当前记录是否已审核 仅当已审核时 从审核日志查找审核人用户名
            if (openReportRecordManager.isAuditOK(dto)) {
                footerMap = addAuditorNameFooter(dto, fileList, footerMap);
            }
        }

        return footerMap;
    }

    private LinkedHashMap<String, Map<String, String>> addAuditorNameFooter(OpenReportRecordDTO dto, FileListDTO fileList, LinkedHashMap<String, Map<String, String>> footerMap) {
        // 查flow_log_cwgk表 根据openId 倒序显示最新一条记录的审核人用户名
        List<FlowLog> logs = flowLogRepository.findByOpenIdOrderById(dto.getId());
        if (logs.size() > 0) {
            String auditorName = logs.get(logs.size() - 1).getWriter();
            FilelistCwgkFooterDto auditorFooter = new FilelistCwgkFooterDto(fileList.getFileName(), "auditorName", "镇街审核", false, null, null);
            Map<String, String> footerValueMap = new HashMap<>(2);
            footerValueMap.put("name", auditorFooter.getFooterName());
            footerValueMap.put("value", auditorName);
            // 指定插入到“会计”脚注的后面
//            footerMap.put(auditorFooter.getFooterKey(), footerValueMap);
            LinkedHashMap<String, Map<String, String>> newFooterMap = new LinkedHashMap<>(footerMap.size() + 1);
//            for (Map.Entry<String, Map<String, String>> entry : footerMap.entrySet()) {
//                newFooterMap.put(entry.getKey(), entry.getValue());
//                if (StringUtil.equalsIgnoreCase(entry.getKey(), PublicConstant.KJXM_KEY)) {
//                    newFooterMap.put(auditorFooter.getFooterKey(), footerValueMap);
//                }
//            }
            // 改成放在第一位
            newFooterMap.put(auditorFooter.getFooterKey(), footerValueMap);
            newFooterMap.putAll(footerMap);
            return newFooterMap;
        } else {
            return footerMap;
        }
    }

    private void addFooterValueMap(Map<String, Map<String, String>> footerMap, FilelistCwgkFooterDto footer, String footerValue) {
        Map<String, String> footerValueMap = new HashMap<>(2);
        footerValueMap.put("name", footer.getFooterName());
        footerValueMap.put("value", footerValue);
        footerMap.put(footer.getFooterKey(), footerValueMap);
    }

    @Override
    public void exportExcelByTemplate(ExportExcelDTO exportExcelDTO, HttpServletResponse response, boolean isTemplate) throws IOException {
        TableDataIdQuery tableDataIdQuery = new TableDataIdQuery();
        tableDataIdQuery.setId(exportExcelDTO.getId());
        TableInfoVO tableInfo = getTableInfo(tableDataIdQuery);
        TableDataQuery tableDataQuery = getTableDataQuery(tableDataIdQuery);
        String tableName = tableInfo.getTableName();
        // 模板路径 每张表单独一个excel模板文件
        String templateFileName = ClassUtils.getDefaultClassLoader().getResource("static/uploads/templates/excel/").getPath()
                                    + tableName + ".xlsx";
        Map<Object, List<Map<String, Object>>> sheetsListData = new HashMap<>(); // key=tableName
        Map<Object, Map<String, Object>> sheetsParamData = new HashMap<>(); // key=tableName

        // 获取表数据
        FileListDTO fileList = fileListManager.getFileListByCache(tableName);
        List<Map<String, Object>> maps;
        if (isTemplate) {
            // 导出的是模板 给6行空数据
            maps = new ArrayList<>();
            for (int i = 0; i < 6; i++) {
                maps.add(new HashMap<>());
            }
        } else {
            List<FileItemDTO> fileItems = fileListManager.listFileItemsByCache(tableName);
            maps = baseDataManager.listTableData(tableDataQuery, fileList, fileItems);
        }
        // 改了 不按sheetName了 改按sheetNo 所以只会为第一张工作表 sheetNo=0
        sheetsListData.put(0, maps);

        // 获取其他信息 如日期 单位名称 脚注
        Map<String, Object> otherParamData = new HashMap<>();
        otherParamData.put("date", tableInfo.getDateStr());
        otherParamData.put("tbdw", tableInfo.getZtName());
        otherParamData.put("footer1", tableInfo.getFooter1());
        otherParamData.put("footer2", tableInfo.getFooter2());
        otherParamData.put("footer3", tableInfo.getFooter3());
        otherParamData.put("footer4", tableInfo.getFooter4());

        sheetsParamData.put(0, otherParamData);

        // 获取子表数据 如果有的话
        MainModalDTO mainModal = getMainModal(tableDataQuery);
        if (mainModal.getSubMainModalList() != null) {
            Map<String, List<Map<String, Object>>> subTableData;
            if (isTemplate) {
                subTableData = new HashMap<>();
                // 导出的是模板 给6行空数据
                for (MainModalDTO subModal : mainModal.getSubMainModalList()) {
                    List<Map<String, Object>> list = new ArrayList<>();
                    for (int i = 0; i < 6; i++) {
                        list.add(new HashMap<>());
                    }
                    subTableData.put(subModal.getTableName(), list);
                }
            } else {
                subTableData = getSubTableData(tableDataQuery, fileList, mainModal);
            }
            // 改用sheetNo作为工作表标识 这里的i+1即为sheetNo
            for (int i = 0; i < mainModal.getSubMainModalList().size(); i++) {
                MainModalDTO subModal = mainModal.getSubMainModalList().get(i);
                // 要区分如果主表不是子表之一的情况 如rep_snj_cw_07自己没有对应数据表 只有07_01, 07_02才是的这种情况 sheetNo会从0开始算起
                int sheetNo = fileList.getNotSubTable() ? i : i + 1;
                sheetsListData.put(sheetNo, subTableData.get(subModal.getTableName()));
                // 各个子表的单位名称、年月、脚注一致
                sheetsParamData.put(sheetNo, otherParamData);
            }
        }

        response.setCharacterEncoding("utf-8");
        try {
            // 导出的excel文件名
            String exportExcelFileName = StringUtil.replace(tableInfo.getDateStr() + fileList.getFileDoc() + "_" + tableInfo.getZtId() + ".xlsx", " ", "");
            response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(exportExcelFileName, "utf-8"));
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
        response.setContentType("application/vnd.ms-excel;charset=gb2312");
        ExcelUtil.writeExcelToWebByTemplate(response.getOutputStream(), templateFileName, sheetsListData, sheetsParamData);

    }

    @Override
    public void exportExcel(ExportExcelDTO exportExcelDTO, HttpServletResponse response) throws IOException {
        TableDataIdQuery tableDataIdQuery = new TableDataIdQuery();
        tableDataIdQuery.setId(exportExcelDTO.getId());
        TableInfoVO tableInfo = getTableInfo(tableDataIdQuery);
        TableDataQuery tableDataQuery = getTableDataQuery(tableDataIdQuery);
        String tableName = tableInfo.getTableName();

        Map<String, List<Map<String, Object>>> sheetsListData = new LinkedHashMap<>(); // key=tableName
        Map<String, Map<String, Object>> sheetsParamData = new LinkedHashMap<>(); // key=tableName
        Map<String, List<ExcelReadFileItem>> titles = new LinkedHashMap<>();

        // 获取表数据
        FileListDTO fileList = fileListManager.getFileListByCache(tableName);
        List<FileItemDTO> fileItems = fileListManager.listFileItemsByCache(tableName);
        titles.put(tableName, ListUtil.toSingleList(fileItems, fileItem -> new ExcelReadFileItem(fileItem.getFieldName(), fileItem.getFieldDoc(), fileItem.getFieldType())));
        List<Map<String, Object>> maps = baseDataManager.listTableData(tableDataQuery, fileList, fileItems);
        if (!fileList.getNotSubTable()) {
            sheetsListData.put(tableName, maps);
        }

        // 获取子表数据 如果有的话
        MainModalDTO mainModal = getMainModal(tableDataQuery);
        if (mainModal.getSubMainModalList() != null) {
            Map<String, List<Map<String, Object>>> subTableData;
            subTableData = getSubTableData(tableDataQuery, fileList, mainModal);
            // 改用sheetNo作为工作表标识 这里的i+1即为sheetNo
            for (int i = 0; i < mainModal.getSubMainModalList().size(); i++) {
                MainModalDTO subModal = mainModal.getSubMainModalList().get(i);
                sheetsListData.put(subModal.getTableName(), subTableData.get(subModal.getTableName()));
            }
        }

        response.setCharacterEncoding("utf-8");
        try {
            // 导出的excel文件名
            String exportExcelFileName = StringUtil.replace(tableInfo.getDateStr() + fileList.getFileDoc() + "_" + tableInfo.getZtId() + ".xlsx", " ", "");
            response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(exportExcelFileName, "utf-8"));
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
        response.setContentType("application/vnd.ms-excel;charset=gb2312");

        ExcelUtil.writeExcelToWeb(response.getOutputStream(), titles, sheetsListData, sheetsParamData);

    }

    private Map<String, List<Map<String, Object>>> getSubTableData(TableDataQuery tableDataQuery, FileListDTO fileList, MainModalDTO mainModal) {
        // 获取子表数据
        Map<String, List<Map<String, Object>>> subData = new HashMap<>();
        for (MainModalDTO subModal : mainModal.getSubMainModalList()) {
            String subTableName = subModal.getTableName();
            // 构造需要的参数 重新调用查询方法
            TableDataQuery subTableDataQuery = new TableDataQuery();
            subTableDataQuery.setYears(tableDataQuery.getYears());
            subTableDataQuery.setMonths(tableDataQuery.getMonths());
            subTableDataQuery.setDays(tableDataQuery.getDays());
            subTableDataQuery.setSno(tableDataQuery.getSno());
            subTableDataQuery.setZtId(tableDataQuery.getZtId());
            subTableDataQuery.setTableName(subTableName);
            subTableDataQuery.setDistNo(tableDataQuery.getDistNo());
            FileListDTO subFileList = getSubFileList(subTableName, fileList);
            List<FileItemDTO> subFileItems = fileListManager.listFileItemsByCache(subTableName);
            // 查询子表信息（这里采取不分页的方式）
            List<Map<String, Object>> subMapPageDTO = baseDataManager.listTableData(subTableDataQuery, subFileList, subFileItems);
            subData.put(subTableName, subMapPageDTO);
        }
        return subData;
    }

    @Override
    @Transactional
    @Deprecated
    public SaveResultVO importExcel(ImportExcelDTO importExcelDTO, File newExcel) throws IOException {

//        OpenReportRecordDTO openReportRecord = openReportRecordManager.getById(importExcelDTO.getId());
//        String tableName = openReportRecord.getTableName();
//        FileListDTO fileList = fileListManager.getFileListByCache(tableName);
//        List<FileItemDTO> fileItems = fileListManager.listFileItemsByCache(tableName);
//        // 存下所有有问题返回给用户的信息
        SaveResultVO saveResultVO = new SaveResultVO();
//        List<String> infoList = new ArrayList<>();
//        TableDataDTO tableDataDTO = new TableDataDTO();
//        setTableDataDTOByOpenReportRecord(tableDataDTO, openReportRecord);
//
//        // 校验该数据是否未校验
//        if (!openReportRecordManager.isEditEnable(openReportRecord)) {
//            saveResultVO.setSuccess(false);
//            saveResultVO.getInfoList().add(tableDataDTO.getId() + " 数据已完成审核，不允许修改，操作取消！");
//            return saveResultVO;
//        }
//
//        // 从excel中获取数据
//        Map<String, List<Map<String, Object>>> tableDataList = new HashMap<>();
////        List<String> sheetList = ExcelUtil.getSheetNames(newExcel);
//        // 要区分如果主表不是子表之一的情况 如rep_snj_cw_07自己没有对应数据表 只有07_01, 07_02才是的这种情况 这里没有数据需要保存
//        if (!fileList.getNotSubTable()) {
//            try {
//                tableDataList.put(tableName, getExcelSheetData(importExcelDTO.getId(), newExcel, tableName, fileItems, 0));
//            } catch (Exception e) {
//                infoList.add(e.getMessage());
//                log.error("", e);
//            }
//        }
//
//        // 获取子表的数据
//        MainModalDTO mainModal = getMainModal(tableDataDTO);
//        if (mainModal.getSubMainModalList() != null) {
//            for (int i = 0; i < mainModal.getSubMainModalList().size(); i++) {
//                MainModalDTO subModal = mainModal.getSubMainModalList().get(i);
//                String subTableName = subModal.getTableName();
//                List<FileItemDTO> subFileItems = fileListManager.listFileItemsByCache(subTableName);
//                try {
//                    // 要区分如果主表不是子表之一的情况 如rep_snj_cw_07自己没有对应数据表 只有07_01, 07_02才是的这种情况 sheetNo会从0开始算起
//                    int sheetNo = fileList.getNotSubTable() ? i : i + 1;
//                    tableDataList.put(subTableName, getExcelSheetData(importExcelDTO.getId(), newExcel, subTableName, subFileItems, sheetNo));
//                } catch (Exception e) {
//                    infoList.add(e.getMessage());
//                    log.error("", e);
//                }
//
//
//            }
//        }
//
//        if (infoList.size() > 0) {
//            saveResultVO.setInfoList(infoList);
//            saveResultVO.setSuccess(false);
//            return saveResultVO;
//        }
//
//        // 保存
//        // 主表
//        tableDataDTO.setTableData(objectMapper.writeValueAsString(tableDataList));
//        saveResultVO = baseDataManager.saveTableData(tableDataDTO, fileList, fileItems);
//
//        // 子表
//        if (mainModal.getSubMainModalList() != null) {
//            saveSubTableData(tableDataDTO, mainModal, saveResultVO);
//        }
//
//        // 导入成功
////        importExcelVO.setInfoList(infoList);
////        importExcelVO.setSuccess(true);
////        return importExcelVO;
//        // 返回导入结果
        return saveResultVO;
    }

    @Transactional
    @Override
    public SaveResultVO importExcelNew(ImportExcelDTO importExcelDTO, MultipartFile newExcel) throws IOException {
        OpenReportRecordDTO openReportRecord = openReportRecordManager.getById(importExcelDTO.getId());

        TableDataDTO tableDataDTO = new TableDataDTO();
        setTableDataDTOByOpenReportRecord(tableDataDTO, openReportRecord);

        // 校验该数据是否未校验
        if (!openReportRecordManager.isEditEnable(openReportRecord)) {
            SaveResultVO saveResultVO = new SaveResultVO();
            getCanNotEditMessage(openReportRecord, saveResultVO);
            return saveResultVO;
        }
        return getDataFromExcelAndSave(tableDataDTO, newExcel, importExcelDTO.getId());
    }

    // 20240424 修改成使用年份、月份、账套号作为条件来导入 不依赖openReportRecord的id
    private SaveResultVO getDataFromExcelAndSave(TableDataDTO tableDataDTO, MultipartFile newExcel, Integer importId) throws JsonProcessingException {
        String tableName = tableDataDTO.getTableName();
        FileListDTO fileList = fileListManager.getFileListByCache(tableName);
        List<FileItemDTO> fileItems = fileListManager.listFileItemsByCache(tableName);

        // 存下所有有问题返回给用户的信息
        SaveResultVO saveResultVO = new SaveResultVO();
        List<String> infoList = new ArrayList<>();

        // 从excel中获取数据
        Map<String, List<Map<String, Object>>> tableDataList = new HashMap<>();
//        List<String> sheetList = ExcelUtil.getSheetNames(newExcel);
        // 要区分如果主表不是子表之一的情况 如rep_snj_cw_07自己没有对应数据表 只有07_01, 07_02才是的这种情况 这里没有数据需要保存
        if (!fileList.getNotSubTable()) {
            try {
                tableDataList.put(tableName, getExcelSheetData(importId, newExcel, tableName, fileItems, 0));
            } catch (Exception e) {
                infoList.add(e.getMessage());
                log.error("", e);
            }
        }

        // 获取子表的数据
        MainModalDTO mainModal = getMainModal(tableDataDTO);
        if (mainModal.getSubMainModalList() != null) {
            for (int i = 0; i < mainModal.getSubMainModalList().size(); i++) {
                MainModalDTO subModal = mainModal.getSubMainModalList().get(i);
                String subTableName = subModal.getTableName();
                List<FileItemDTO> subFileItems = fileListManager.listFileItemsByCache(subTableName);
                try {
                    // 要区分如果主表不是子表之一的情况 如rep_snj_cw_07自己没有对应数据表 只有07_01, 07_02才是的这种情况 sheetNo会从0开始算起
                    int sheetNo = fileList.getNotSubTable() ? i : i + 1;
                    tableDataList.put(subTableName, getExcelSheetData(importId, newExcel, subTableName, subFileItems, sheetNo));
                } catch (Exception e) {
                    infoList.add(e.getMessage());
                    log.error("", e);
                }


            }
        }

        if (infoList.size() > 0) {
            saveResultVO.setInfoList(infoList);
            saveResultVO.setSuccess(false);
            return saveResultVO;
        }

        // 保存
        // 主表
        tableDataDTO.setTableData(objectMapper.writeValueAsString(tableDataList));
        saveResultVO = baseDataManager.saveTableData(tableDataDTO, fileList, fileItems);

        // 子表
        if (mainModal.getSubMainModalList() != null) {
            saveSubTableData(tableDataDTO, mainModal, saveResultVO);
        }

        // 导入成功 返回导入结果
        return saveResultVO;
    }

    private void saveSubTableData(TableDataDTO tableDataDTO, MainModalDTO mainModal, SaveResultVO saveResultVO) throws JsonProcessingException {
//        SaveResultVO saveResultVO = null;
        for (MainModalDTO subModal : mainModal.getSubMainModalList()) {
            TableDataDTO subTableDataDTO = new TableDataDTO();
            subTableDataDTO.setYears(tableDataDTO.getYears());
            subTableDataDTO.setMonths(tableDataDTO.getMonths());
            subTableDataDTO.setDays(tableDataDTO.getDays());
            subTableDataDTO.setSno(tableDataDTO.getSno());
            subTableDataDTO.setZtId(tableDataDTO.getZtId());
            subTableDataDTO.setTableName(subModal.getTableName());
            subTableDataDTO.setDistNo(tableDataDTO.getDistNo());
            subTableDataDTO.setTableData(tableDataDTO.getTableData());
            FileListDTO subFileList = fileListManager.getFileListByCache(subModal.getTableName());
            List<FileItemDTO> subFileItems = fileListManager.listFileItemsByCache(subModal.getTableName());
            SaveResultVO subSaveResultVO = baseDataManager.saveTableData(subTableDataDTO, subFileList, subFileItems);
            if (!subSaveResultVO.isSuccess()) {
                // 若子表保存有出错，则将信息附加到已有的保存反馈结果中
                saveResultVO.setSuccess(false);
                saveResultVO.getInfoList().addAll(subSaveResultVO.getInfoList());
            }
        }
//        return saveResultVO;
    }

//    private List<Map<String, Object>> getExcelSheetData(Integer id, File newExcel, String tableName, List<FileItemDTO> fileItems, int sheetNo) {
//        OvertExcelReadListener excelReadListener = new OvertExcelReadListener();
//        excelReadListener.setExcelFileItems(ListUtil.toSingleList(fileItems,
//                fileItem -> new ExcelReadFileItem(fileItem.getFieldName(), fileItem.getFieldDoc(), fileItem.getFieldType())));
//        List<Map<String, Object>> excelDataList;
//        try {
//            // 改了 使用sheetNo作为工作表标识
//            excelDataList = ExcelUtil.readExcelFromFile(newExcel, sheetNo, 5,  excelReadListener);
//        } catch (Exception e) {
//            throw new RuntimeException(id + " " + tableName + "导入excel异常：", e);
//        }
//        if (excelDataList.size() == 0) {
//            throw new RuntimeException(id + " " + tableName + "导入excel没有内容！");
//        }
//        int fieldCount = excelDataList.get(0).size();
//        if (fieldCount != fileItems.size()) {
//            throw new RuntimeException(id + " " + tableName + " 表头字段个数不符！实为" + fieldCount + "，应为" + fileItems.size());
//        }
//
//
//        // 打包格式
//        // 给每一行补充ID字段的值 全部给0 表示每一行都是新增 是清空导入策略
//        for (Map<String, Object> eachLine : excelDataList) {
//            eachLine.put("ID", 0);
//        }
//
//        return excelDataList;
//    }

    // 直接从multipartFile接收的Excel文件的输入流
    private List<Map<String, Object>> getExcelSheetData(Integer id, MultipartFile newExcel, String tableName, List<FileItemDTO> fileItems, Object sheetKey) {
        OvertExcelReadListener excelReadListener = new OvertExcelReadListener();
        excelReadListener.setExcelFileItems(ListUtil.toSingleList(fileItems,
                fileItem -> new ExcelReadFileItem(fileItem.getFieldName(), fileItem.getFieldDoc(), fileItem.getFieldType())));
        List<Map<String, Object>> excelDataList;
        try {
            // 改了 使用sheetNo作为工作表标识
            if (sheetKey instanceof Integer) {
                excelDataList = ExcelUtil.readExcelFromWeb(newExcel, (Integer) sheetKey, 1, excelReadListener);
            } else {
                excelDataList = ExcelUtil.readExcelFromWeb(newExcel, (String) sheetKey, 1, excelReadListener);
            }
        } catch (Exception e) {
            throw new RuntimeException(id + " " + tableName + "导入excel异常：" + e.getMessage(), e);
        }
        if (excelDataList.size() == 0) {
            throw new RuntimeException(id + " " + tableName + "导入excel没有内容！");
        }
        // 表头字段校验挪到了监听类内
//        int fieldCount = excelDataList.get(0).size();
//        if (fieldCount != fileItems.size()) {
//            throw new RuntimeException(id + " " + tableName + " 表头字段个数不符！实为" + fieldCount + "，应为" + fileItems.size());
//        }

        return excelDataList;
    }


    @Transactional
    @Override
    public List<SaveResultVO> importAccess(ImportAccessCommand importAccessCommand, File accessFile, Consumer<Void> advanceProgressConsumer) throws Exception {
        //实际执行的业务需要手动开启事务、手动提交回滚
        // 已有@Transactional注解 注释掉
//        DefaultTransactionDefinition def = new DefaultTransactionDefinition();
//        TransactionStatus primaryStatus = transactionManagerPrimary.getTransaction(def);

        List<SaveResultVO> saveResultVOS = new ArrayList<>();
        SaveResultVO saveResultVO = new SaveResultVO();
        saveResultVOS.add(saveResultVO);


        // 获取access数据库连接
        try (Connection conToAccess = AccessDBUtil.initConnection(accessFile, "", "")) {
            Statement fromSm = conToAccess.createStatement();

            // readyDistInfo 读取地区信息----------------------------------
            String fromDistId, toDistId, fromDistName;
            Date fromDate1, fromDate2;
            boolean fromDateAllFlag;
            // 获取access的
            List<List<Object>> accessDists = AccessDBUtil.getList(fromSm, "select distid,distName,date1,date2,date3,dateallflag from s_update");
            if (accessDists.size() > 0) {
                List<Object> accessDist = accessDists.get(0);
                fromDistId = (String) accessDist.get(0);
                fromDistName = (String) accessDist.get(1);
                fromDate1 = DateUtil.parseDateString((String) accessDist.get(2));
                fromDate2 = DateUtil.parseDateString((String) accessDist.get(3));
                fromDateAllFlag = (boolean) accessDist.get(5);
            } else {
                throw new RuntimeException("准备工作readyDistInfo 读取access 地区信息错误: 获取不到s_update数据！");
            }
            // 获取区资产库的
            DistDTO toDist = distZtManager.getDistByDistNameByCache(fromDistName);
            if (toDist != null) {
                toDistId = toDist.getDistNo();
            } else {
                throw new RuntimeException("准备工作readyDistInfo 读取access 地区信息错误: 平台未取到地区“" + fromDistName + "”！");
            }
            log.debug("readyDistInfo完成");

            // readyZts 读取账套信息-------------------------------------------
            List<List<Object>> accessZts = AccessDBUtil.getList(fromSm, "select zth from zt where ztparent like '" + fromDistId + "%'");
            List<String> fromZts = new ArrayList<>();
            for (List<Object> accessZt : accessZts) {
                String zth = StringUtil.trimToEmpty((String) accessZt.get(0));
                zth = toDistId + StringUtil.right(zth, zth.length() - fromDistId.length());

//                String controlField = Constants.controlField;
//                if (!Constants.controlField.equalsIgnoreCase("")) {
//                    zth = getZth(StringUtil.trimToEmpty((String)((List)fromList.get(i)).get(0)), toDistId);
//                }
                if (!StringUtil.isEmpty(zth)) {
                    fromZts.add(zth);
                }
            }
            if (fromZts.size() == 0) {
                throw new RuntimeException("准备工作readyZts 读取access 账套信息错误: fromZts无数据！");
            }
            log.debug("readyZts完成");


            // workDist 采集dist表 从access取dist信息-------------------------
            // 标记进度条总项数
            int gaugeId = 0;
            String sql = "select '" + toDistId + "'+right(地区编号,len(地区编号)-len('" + fromDistId + "')),地区名称 from dist where 地区编号 like '" +
                    fromDistId + "%'";
            accessDists = AccessDBUtil.getList(fromSm, sql);
            for (List<Object> accessDist : accessDists) {
                gaugeId += 1;

                String distId = StringUtil.trimToEmpty((String) accessDist.get(0));
                String distName = StringUtil.trimToEmpty((String) accessDist.get(1));
                // 若区资产平台不存在该地区 则新增
                if (distZtManager.getDistByDistNoByCache(distId) == null) {
                    DistDTO newDist = new DistDTO();
                    newDist.setDistNo(distId);
                    newDist.setDistName(distName);
                    distZtManager.insertNewDist(newDist);
                }
            }
            log.debug("workDist完成");

            // workZt
            // 区平台资产库的账套号列表//            List<String> toZts = new ArrayList<>();
            // 保存账套对应关系 key=access的账套号（zth_import） value= 区平台账套号ztId
            Map<String, String> ztMap = new HashMap<>();
            String queryZtSql;
            List<String> ztColumns = AccessDBUtil.getTableColumns(fromSm, "zt");
            // 检测access中的账套表是否存在zbr dwfzr kjxm cnxm jdxz这些字段
            if (!ztColumns.contains("zbr") || !ztColumns.contains("dwfzr") || !ztColumns.contains("kjxm")
                    || !ztColumns.contains("cnxm") || !ztColumns.contains("jdxz")) {
                queryZtSql = "select zth,ztdw,'" + toDistId + "',zth_old,checked,'' as zbr, '' as dwfzr, ''as kjxm, '' as  cnxm, '' as jdxz  from zt where ztparent like '" + fromDistId + "%'";
            } else {
                queryZtSql = "select zth,ztdw,'" + toDistId + "',zth_old,checked,zbr,  dwfzr, kjxm,  cnxm,  jdxz  from zt where ztparent like '" + fromDistId + "%'";
            }
            accessZts = AccessDBUtil.getList(fromSm, queryZtSql);
            for (List<Object> accessZt : accessZts) {
                gaugeId += 1;

                // 根据zth_import找出对应区平台账套
                String zthImport = (String) accessZt.get(0);
                ZtDTO importZt = distZtManager.findByZthImportByCache(zthImport);
                if (importZt == null) {
                    continue;
                }
                String zth = importZt.getZtId();
//                toZts.add(zth);
                ztMap.put(zthImport, zth);
//                String ztdw = StringUtil.trimToEmpty((String) accessZt.get(1));
//                String ztparent = StringUtil.trimToEmpty((String) accessZt.get(2));
//                String zth_old = StringUtil.trimToEmpty((String) accessZt.get(3));
//                boolean checked = (Boolean) accessZt.get(4);
                String zbr = StringUtil.trimToEmpty((String) accessZt.get(5));
                String dwfzr = StringUtil.trimToEmpty((String) accessZt.get(6));
                String kjxm = StringUtil.trimToEmpty((String) accessZt.get(7));
                String cnxm = StringUtil.trimToEmpty((String) accessZt.get(8));
                String jdxz = StringUtil.trimToEmpty((String) accessZt.get(9));

                Map<String, Object> updateZtParams = new HashMap<>();
                if (StringUtil.isNotEmpty(zbr)) {
                    updateZtParams.put("zbr", zbr);
                }
                if (StringUtil.isNotEmpty(dwfzr)) {
                    updateZtParams.put("dwfzr", dwfzr);
                }
                if (StringUtil.isNotEmpty(kjxm)) {
                    updateZtParams.put("kjxm", kjxm);
                }
                if (StringUtil.isNotEmpty(cnxm)) {
                    updateZtParams.put("cnxm", cnxm);
                }
                if (StringUtil.isNotEmpty(jdxz)) {
                    updateZtParams.put("jdxz", jdxz);
                }
                // 更新区平台的zbr dwfzr等字段
                distZtManager.updateZtParamsByZtId(zth, updateZtParams);
            }
            log.debug("workZt完成");

            // workTable 插入数据
            String fromYears1 = StringUtil.trimToEmpty(DateUtil.getYearOrMonthOrDay(fromDate1, Calendar.YEAR));
            String fromYears2 = StringUtil.trimToEmpty(DateUtil.getYearOrMonthOrDay(fromDate2, Calendar.YEAR));
            String fromMonths1 = StringUtil.trimToEmpty(DateUtil.getYearOrMonthOrDay(fromDate1, Calendar.MONTH));
            String fromMonths2 = StringUtil.trimToEmpty(DateUtil.getYearOrMonthOrDay(fromDate2, Calendar.MONTH));

            for (String tableName : importAccessCommand.getTableNames()) {
                FileListDTO fileList = fileListManager.getFileListByCache(tableName);
                List<FileItemDTO> fileItems = fileListManager.listFileItemsByCache(tableName);
                if (fileItems.size() == 0) {
                    saveResultVO.setSuccess(false);
                    saveResultVO.getInfoList().add(tableName + " " + fileList.getFileDoc() +  " 未配置表头！导入未完成！");
//                } else {
//                    try {
//                        // 先检查access文件中是否有这张表
//                        AccessDBUtil.getList(fromSm, "select count(*) from [" + tableName + "]");
//                    } catch (Exception e) {
//                        saveResultVO.setSuccess(false);
//                        saveResultVO.getInfoList().add("检验失败：" + tableName + " " + fileList.getFileDoc() +  " 该表在导入文件中不存在！");
//                        log.error(tableName + "检验出错（Access）" + accessFile.getName(), e);
//                    }
                }
            }
            log.debug("workTable检验完毕 检验结果：" + saveResultVO.isSuccess());

            if (saveResultVO.isSuccess()) {
                for (String tableName : importAccessCommand.getTableNames()) {
                    // 先检查access文件中是否有这张表
                    try {
                        AccessDBUtil.getList(fromSm, "select count(*) from [" + tableName + "]");
                    } catch (Exception e1) {
                        log.debug("检验Access " + tableName + "报错，跳过该表", e1);
                        // 若Access中没这张表 则跳过
                        advanceProgressConsumer.accept(null);
                        continue;
                    }
                    FileListDTO fileList = fileListManager.getFileListByCache(tableName);
                    List<FileItemDTO> fileItems = fileListManager.listFileItemsByCache(tableName);
                    try {
                        deleteTableData(fileList, new ArrayList<>(ztMap.values()), fromYears1, fromYears2, fromMonths1, fromMonths2, fromDateAllFlag);
                        insertTableDataByAccess(fromSm, fileList, fileItems, fromDistId, fromYears1, fromYears2, fromMonths1, fromMonths2, fromDateAllFlag, ztMap);
                        log.debug(tableName + "完成");
                    } catch (Exception e) {
                        saveResultVO.setSuccess(false);
                        saveResultVO.getInfoList().add(tableName + "导入出错！" + e.getMessage());
                        log.error(tableName + "导入出错（Access）" + accessFile.getName(), e);
                    }
                    // 设置进度条 完成当前表
//                    executor.advanceProgress();
                    advanceProgressConsumer.accept(null);
                }
            }
            log.debug("workTable完成");

            // workShenhe 审核全部未审核数据
            // 这里可能还要生成缺少的openReportRecord的数据 也可以当openReportRecord已经有数据 缺再手动生成
            OpenReportRecordQuery query = new OpenReportRecordQuery();
            query.setDistNo(toDistId);
            query.setYwType(PublicConstant.GK_TYPE_CW);
            // TODO 这里要搜索一个区间的数据
            query.setYears(Integer.parseInt(fromYears1));
            query.setMonths(Integer.parseInt(fromMonths1));
            // 这里设置查询所有未审核的数据 包括待录入status为空的
            query.setIsAudit(false);

            List<OpenReportRecordDTO> openList = openReportRecordManager.listByCondition(query, PublicConstant.LIST_TYPE_EDIT);
            for (OpenReportRecordDTO openDTO : openList) {
                openReportRecordManager.setAuditSuccessAndReadyPublishStatus(openDTO, "AccessImport");
            }
            openReportRecordManager.saveAll(openList);
            log.debug("workShenhe完成");

        } catch (Exception e) {
            saveResultVO.setSuccess(false);
            saveResultVO.getInfoList().add("导入出错！" + e.getMessage());
            log.error("导入Access出错：" + accessFile.getName(), e);
        }
        return saveResultVOS;
    }

    private void deleteTableData(FileListDTO fileList, List<String> ztIds, String fromYears1, String fromYears2, String fromMonths1, String fromMonths2, boolean isAllDate) {
        String deleteSQL = "delete from \"" + fileList.getFileName() + "\" where zth in (:ztIds) ";
        Map<String, Object> params = new HashMap<>();
        if (!isAllDate) {
            if (fileList.getPublicType() == PublicType.YEAR) {
                deleteSQL += " and years>=:fromYears and years<=:toYears";
                params.put("fromYears", fromYears1);
                params.put("toYears", fromYears2);
            } else {
                deleteSQL += " and (years*100+months)>=:fromYearMonth and (years*100+months)<=:toYearMonth";
                params.put("fromYearMonth", fromYears1 + fromMonths1);
                params.put("toYearMonth", fromYears2 + fromMonths2);
            }
        }

        params.put("ztIds", ztIds);
        EntityManagerUtil.update(entityManager, deleteSQL, params);
    }

    private void insertTableDataByAccess(Statement fromSm, FileListDTO fileList, List<FileItemDTO> fileItems, String fromDistId, String fromYears1,
                                         String fromYears2, String fromMonths1, String fromMonths2, boolean fromDateAllFlag,
                                         Map<String, String> ztMap) throws Exception {
        String tableName = fileList.getFileName();

        // 检验是否有months字段
        List<String> tableColumns = AccessDBUtil.getTableColumns(fromSm, tableName);
        boolean hasMonths = tableColumns.contains("months") || tableColumns.contains("Months");
        if (!hasMonths && (fileList.getPublicType() != PublicType.YEAR)) {
            throw new RuntimeException("查询access子表数据时报错：" + tableName + "不是年表，但没有months列！");
        }

        // 查询access中的数据 是按照年份和月份区间将所有数据全部查询出来
        List<String> fields = ListUtil.toSingleList(fileItems, FileItemDTO::getFieldName);
        String columnSql = StringUtil.join(fields, ", ") + ", years, " + (hasMonths ? "months, " : "") + "zth ";
        String sql = "select " +
                columnSql +
                " from [" +
                tableName +
                "] where zth in ( select zth from zt where ztparent like '" +
                fromDistId + "%')";
        if (!fromDateAllFlag) {
            if (fileList.getPublicType() == PublicType.YEAR) {
                sql += " and years>=" + fromYears1 + " and years<=" + fromYears2;
            } else {
                sql += " and (years*100+months)>=" + fromYears1 + fromMonths1 + " and (years*100+months)<=" + fromYears2 + fromMonths2;
            }
            sql += " order by " + (StringUtil.isEmpty(fileList.getOrderField()) ? "serial" : fileList.getOrderField());
        }

        List<List<Object>> accessDataList;
        try {
            accessDataList = AccessDBUtil.getList(fromSm, sql);
        } catch (Throwable throwable) {
            throwable.printStackTrace();
            throw new RuntimeException("查询access数据失败：" + sql + ", " + throwable.getMessage());
        }

        List<Map<String, Object>> dataList = new ArrayList<>();
        for (List<Object> accessData : accessDataList) {
            Map<String, Object> dataMap = new HashMap<>();
            for (int i = 0; i < fields.size(); i++) {
                dataMap.put(fields.get(i).toUpperCase(), accessData.get(i));
            }
            int count = fields.size();
            dataMap.put("YEARS", accessData.get(count));
            count++;
            if (hasMonths) {
                dataMap.put("MONTHS", accessData.get(count));
                count++;
            }
            String ztId = ztMap.get((String) accessData.get(count));
            if (ztId != null) {
                dataMap.put("ZTID", ztId);
                dataList.add(dataMap);
            }
        }

        baseDataManager.insertTableDataByArray(dataList, fileList, fileItems);
    }

//    @Transactional
//    @Override
//    public List<CollectPlatformAsyncTaskVO> importAccess(ImportAccessCommand importAccessCommand, File accessFile) throws Exception {
//        String username = importAccessCommand.getUserName();
//
//
//        List<CollectPlatformAsyncTaskVO> collectPlatformAsyncTaskVOS = new ArrayList<>();
//        String keyword = UUID.randomUUID().toString();
//
//        //1.2. 校验通过生成一个记录信息，可以让用户实时感知到上传的情况
//        //1.3. 异步执行任务、监控该任务执行的状态和进度、 还要能够查看实际的采集情况
//        AsyncAccessImportExecutor asyncTaskDTO = new AsyncAccessImportExecutor();
//        asyncTaskDTO.setKeyword(keyword);
//        asyncTaskDTO.setTaskType("导入Access");
//        asyncTaskDTO.setUsername(username);
//        asyncTaskDTO.setTaskName("导入Access");
//
//        //设置任务的事务管理器
//        asyncTaskDTO.setTransactionManagerPrimary(transactionManagerPrimary);
//
//        //设置数据库操作工具
//        asyncTaskDTO.setJdbcTemplatePrimary(jdbcTemplatePrimary);
//
//        asyncTaskDTO.setDistZtManager(distZtManager);
//        asyncTaskDTO.setOpenReportRecordManager(openReportRecordManager);
//        asyncTaskDTO.setFileListManager(fileListManager);
//        asyncTaskDTO.setBaseDataManager(baseDataManager);
//        asyncTaskDTO.setEntityManager(entityManager);
//
//        asyncTaskDTO.setTableNames(importAccessCommand.getTableNames());
//        asyncTaskDTO.setAccessFile(accessFile);
//
//        /*
//         * 同步锁的标识值 这里任务根据文件名来锁
//         */
//        asyncTaskDTO.setSyncKeyword(accessFile.getName());
//
//        //开始异步执行任务
//        CollectPlatformAsyncTaskVO collectPlatformAsyncTaskVO = new CollectPlatformAsyncTaskVO();
//        collectPlatformAsyncTaskVO.setKeyword(keyword);
//
//        AsyncTaskResultVO asyncTaskResultVO = asyncMessageService.execAsyncTask(asyncTaskDTO);
//        //如果任务成功提交
//        if (asyncTaskResultVO != null && asyncTaskResultVO.getIsSubmitTask()) {
//            collectPlatformAsyncTaskVO.setStatus(IN_PROGRESS.getMessage());
//            collectPlatformAsyncTaskVO.setRemark("任务执行中。。。");
//        } else {
//            collectPlatformAsyncTaskVO.setStatus(ABANDON_EXECUTION.getMessage());
//            collectPlatformAsyncTaskVO.setRemark("执行失败");
//            collectPlatformAsyncTaskVO.setNoSubmitIllustrate(asyncTaskResultVO != null ? asyncTaskResultVO.getNoSubmitIllustrate() : null);
//        }
//        collectPlatformAsyncTaskVOS.add(collectPlatformAsyncTaskVO);
//        return collectPlatformAsyncTaskVOS;
//    }

    @Override
    public List<AttachedVO> viewAttachedFiles(TableDataIdQuery tableDataIdQuery) {
        List<Attached> attachedList = attachedRepository.findByOpenId(tableDataIdQuery.getId());
        List<AttachedDTO> attachedDTOS = attachedConverter.poConvertDTO(attachedList);
        List<AttachedVO> attachedVOS = new ArrayList<>();
        for (AttachedDTO attachedDTO : attachedDTOS) {
            AttachedVO attachedVO = attachedConverter.dtoConvertVO(attachedDTO);
            attachedVO.setDownloadUrl("" + attachedDTO.getUploadPath() + attachedDTO.getNewFileName());
            attachedVOS.add(attachedVO);
        }
        return attachedVOS;
    }

    @Override
    public void saveAttachedFiles(List<AttachedDTO> attachedDTOS, TableDataIdQuery tableDataIdQuery) {
        TableDataQuery tableDataQuery = getTableDataQuery(tableDataIdQuery);
        for (AttachedDTO attachedDTO : attachedDTOS) {
            attachedDTO.setYears(tableDataQuery.getYears());
            attachedDTO.setMonths(tableDataQuery.getMonths());
            attachedDTO.setDays(tableDataQuery.getDays());
            attachedDTO.setZtId(tableDataQuery.getZtId());
            attachedDTO.setSno(tableDataQuery.getSno());
            attachedDTO.setTableName(tableDataQuery.getTableName());
            attachedDTO.setUploadTime(new Date());
            attachedDTO.setOpenId(tableDataIdQuery.getId());
        }

        attachedRepository.saveAll(attachedConverter.dtoConvertPO(attachedDTOS));
    }

    @Override
    public void deleteAttachedData(List<Integer> attachedIds) {
        List<Attached> allById = attachedRepository.findAllById(attachedIds);
        attachedRepository.deleteAll(allById);
    }

    @Override
    public SaveResultVO deleteOpenReportRecordByIds(int[] ids) {
        SaveResultVO saveResultVO = new SaveResultVO();
        List<Integer> idList = ListUtil.toIntegerListByArray(ids);
        List<OpenReportRecordDTO> openList = openReportRecordManager.listById(idList);
        List<OpenReportRecordDTO> allowDeleteList = new ArrayList<>();
        for (OpenReportRecordDTO dto : openList) {
            // v1.0.2 删除这里不用isEditEnable 改成只判断是否已审核 否则不可编辑的公开表未审核也删除不了
            if (openReportRecordManager.isAuditOK(dto)) {
                allowDeleteList.add(dto);
            }
        }
        if (allowDeleteList.size() == 0) {
            saveResultVO.setSuccess(false);
            saveResultVO.setMessage("数据已审核或发布，不可删除，操作取消！");
            return saveResultVO;
        }
        openReportRecordManager.deleteAll(allowDeleteList);
        saveResultVO.setMessage("删除完成！");
        return saveResultVO;
    }

    @Override
    @Transactional
    public void autoCreateOpenReportRecord(OpenReportRecordQuery query) {
        Integer years = query.getYears();
        Integer months = query.getMonths();

        Map<String, Object> params = new HashMap<>();
        params.put("dates", years + "-" + months + "-" + "01");
        EntityManagerUtil.update(entityManager, "exec proc_createOpenReportRecord 2, :dates", params);
    }

    @Override
    public List<ButtonVO> getPrtBtns(TableDataIdQuery tableDataIdQuery) {
        OpenReportRecordDTO openReportRecord = openReportRecordManager.getById(tableDataIdQuery.getId());
        String tableName = openReportRecord.getTableName();
        List<NavigateButtonCwgk> buttons = fileListManager.getButtonsByFileName(tableName);
        List<ButtonVO> result = new ArrayList<>(buttons.size());
        // v1.0.1 仅在已审核的数据中才显示打印按钮
        if (openReportRecordManager.isAuditOK(openReportRecord)) {
            for (NavigateButtonCwgk button : buttons) {
                // 处理打印网址中的占位符
                String url = button.getUrl();
                url = StringUtil.replace(url, "${years}", String.valueOf(openReportRecord.getYears()));
                url = StringUtil.replace(url, "${months}", String.valueOf(openReportRecord.getMonths()));
                url = StringUtil.replace(url, "${ztId}", openReportRecord.getZtId());
                result.add(new ButtonVO(button.getName(), button.getShowText(), url));
            }
        }
        return result;
    }
}
